const mysql = require('mysql2/promise');
const dotenv = require('dotenv');
const dotenvExpand = require('dotenv-expand');
const groupTablesConfig = require('./groupTablesConfig');

// 扩展环境变量支持
const myEnv = dotenv.config();
dotenvExpand.expand(myEnv);

// MySQL连接池配置
const pool = mysql.createPool({
  host: process.env.MYSQL_HOST || 'localhost',
  user: process.env.MYSQL_USER || 'root',
  password: process.env.MYSQL_PASSWORD || '123456',
  database: process.env.MYSQL_DATABASE || 'litechat',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// 初始化数据库
const initializeDatabase = async () => {
  try {
    const connection = await pool.getConnection();
    
    console.log('连接到MySQL数据库成功');
    
    // 创建数据库（如果不存在）
    await connection.query(`CREATE DATABASE IF NOT EXISTS ${process.env.MYSQL_DATABASE || 'litechat'}`);
    
    // 切换到litechat数据库
    await connection.query(`USE ${process.env.MYSQL_DATABASE || 'litechat'}`);
    
    // 创建好友关系表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS friends (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id VARCHAR(255) NOT NULL,
        friend_id VARCHAR(255) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX (user_id),
        INDEX (friend_id),
        UNIQUE KEY unique_friendship (user_id, friend_id)
      )
    `);
    
    // 创建好友请求表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS friend_requests (
        id INT AUTO_INCREMENT PRIMARY KEY,
        from_user_id VARCHAR(255) NOT NULL,
        to_user_id VARCHAR(255) NOT NULL,
        status ENUM('pending', 'accepted', 'rejected') DEFAULT 'pending',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX (from_user_id),
        INDEX (to_user_id),
        UNIQUE KEY unique_request (from_user_id, to_user_id)
      )
    `);
    
    // 创建群组相关表
    await connection.query(groupTablesConfig.groups);
    await connection.query(groupTablesConfig.group_members);
    await connection.query(groupTablesConfig.group_invitations);
    await connection.query(groupTablesConfig.group_join_requests);
    
    console.log('群组相关表初始化成功');
    
    // 检查好友关系表是否为空
    const [friendRows] = await connection.query('SELECT COUNT(*) as count FROM friends');
    if (friendRows[0].count === 0) {
      console.log('好友关系表为空，检查是否需要从聊天记录中迁移好友关系');
      
      // 如果需要，可以在此调用迁移功能
      // 但由于涉及MongoDB连接，最好通过外部脚本执行，而不是在此处直接执行
      console.log('请运行 node migration.js 来自动迁移好友关系');
    } else {
      console.log(`好友关系表中有 ${friendRows[0].count} 条记录`);
    }
    
    console.log('MySQL数据库初始化成功');
    connection.release();
    return true;
  } catch (error) {
    console.error('MySQL数据库初始化失败:', error);
    return false;
  }
};

// 执行单个查询
const query = async (sql, params) => {
  try {
    const [results] = await pool.execute(sql, params);
    return results;
  } catch (error) {
    console.error('MySQL查询执行失败:', error);
    throw error;
  }
};

module.exports = {
  pool,
  query,
  initializeDatabase
}; 